Imports System.Data.OleDb 'acesso ao access
''' <summary>
''' Para acessar os objetos ADODB precisa Projeto, Adicionar Referencia, Assemblies, Extensions, ADODB
''' </summary>
Module Access
Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=pasta\arquivo.mdb"
Public Function PreecheDDLComboBox() As Boolean
Dim sql As String
Dim cmd As OleDbCommand
Dim conn As New OleDbConnection(constr)
Dim a As String 'text do dropDownList
Dim b As String 'value do dropDownList
Dim cbb As New ComboBox 'no aspx é dropdownlist
'lblmsg.Text = ""
'If Not IsPostBack Then
sql = "SELECT T1,N1 FROM COLABORADORES" 't1=nome, n1=FIXO-RM-ID
cmd = New OleDbCommand(sql, conn)
Try
'colaboradores
cbb.Items.Clear()
'ddlColaboradores.Items.Add(New ListItem("", ""))
cbb.Items.Add("")
conn.Open()
Dim read As OleDbDataReader = cmd.ExecuteReader()
If read.HasRows Then
While read.Read()
a = read.Item("T1").ToString
b = read.Item("N1").ToString
'ddlColaboradores.Items.Add(New ListItem(a, b))
cbb.Items.Add(a + vbTab + b)
End While
End If
read.Close()
Return True
Catch ex As Exception
'lblmsg.Text = ex.Message
Return False
Finally
conn.Close()
End Try
End Function
''' <summary>
''' Executa uma ou mais queries
''' As queries podem ser agrupadas em um único string separadas por CRLF
''' </summary>
''' <param name="Querie"></param>
''' <returns></returns>
'''
Public Function ExecutarQuerie(Querie As String) As Boolean 'btnExecutar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExecutar.Click
Dim Comando As New OleDbCommand
Dim ConexaoBD As New OleDbConnection
Dim SenhaBD As String = ""
Dim Sql As String
'Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Colegio Santa Isabel\BD Site1 2014\" + lstBD.Text + ".mdb"
Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=e:\pastalstBD.mdb"
Dim a As Integer
Dim queries() As String
'btnExecutar.Enabled = False
queries = Split(Querie, vbCrLf)
ConexaoBD = New OleDbConnection(constr)
ConexaoBD.Open()
Try
For a = 0 To UBound(queries)
'txtandamento.Text = CStr(a)
Sql = queries(a)
If Sql <> "" Then
Comando = New OleDbCommand(Sql, ConexaoBD) 'Passando a SQL e a conexão
'Comando.Parameters.AddWithValue("@Nome", "Teste")
Comando.ExecuteNonQuery()
Comando.Dispose()
End If
Next
Catch ex As Exception
'lblmsg.Text = ex.Message + vbCrLf + queries(a)
Return False
End Try
'lblmsg.Text = "FIM"
'btnExecutar.Enabled = True
Return True
End Function
''' <summary>
''' Executa um SQL que retorna dados num Datagrid
''' </summary>
''' <param name="Querie"></param>
''' <param name="tabela"></param>
''' <returns>um datagrid</returns>
''' Exemplo : Querie = "Select * from Clientes" e tabela = Clientes
Public Function ExecutaSQL(Querie As String, tabela As String) As DataGrid
Dim strcon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Meus Docs DeskTop\Visual Studio 2010\Projects\129-AcessoAccess\AcessoAccess.mdb;"
Dim Con As New OleDb.OleDbConnection(strcon)
Dim sql As String = Querie
Dim da As New OleDb.OleDbDataAdapter(sql, Con)
Dim ds As New DataSet
Dim dg As New DataGrid
da.Fill(ds, tabela) 'como desejo chamar a tabela
dg.DataSource = ds.Tables(tabela) 'qual tabela desejo do dataset
dg.Refresh()
Application.DoEvents()
Con.Close()
da.Dispose()
ds.Dispose()
Return dg
End Function
'#############################################################################################################
'# as funçãoes abaixo mencionadas executam todas as funcionalidades de acesso as bases de dados ACCESS
'#############################################################################################################
''' <summary>
''' Para acessar os objetos ADODB precisa Projeto, Adicionar Referencia, Assemblies, Extensions, ADODB
''' </summary>
Public BDCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Ref_Tec_Visual_Studio_2010\RefTecVS2010.mdb;Jet OLEDB:Database Password=Presidencialismo;" ' String de conexão com banco de dados
Public bd As ADODB.Connection ' banco de dados
Public rs As ADODB.Recordset ' conjunto de registros retornados de uma tabela do banco de dados
Public sql As String ' pesquisa a ser feita no banco de dados
Public erro As String
Public Function BDretornaDataSet(ByVal query As String) As DataSet
'NÃO utilizar com o DataGridView
erro = ""
Try
Dim ds As New DataSet
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(ds)
Conexao.Close()
Return ds
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Public Function BDretornaDataAdapter(ByVal query As String) As OleDb.OleDbDataAdapter
'testado ok
'não usar como fonte do DataGRidView
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb)
Debug.Print(datatb.Rows.Count)
Conexao.Close()
Return dataadp
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Public Function BDretornaTabelaDataAdapter(ByVal query As String, ByVal Tabela As String) As OleDb.OleDbDataAdapter
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb, Tabela)
Conexao.Close()
Return dataadp
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Public Function BDretornaDataTable(ByVal query As String) As DataTable
'utilizar com o DataGridView
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb)
Conexao.Close()
Return datatb
'dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
''' <summary>
''' Executa uma querie que não retorna dados - tipo delete
''' </summary>
''' <param name="query"></param>
Public Sub BDExecutaComando(ByVal query As String)
erro = ""
Try
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Conexao.Open()
Dim comando As New OleDbCommand(query, Conexao)
comando.ExecuteNonQuery()
Conexao.Close()
Catch ex As Exception
erro = ex.Message
End Try
End Sub
End Module